--member table 생성
create table uri_member(
   m_id varchar2(50) primary key,
   m_password varchar2(50) not null,
   m_name varchar2(50) not null,
   m_ssn varchar2(50) not null,
   m_address varchar2(50) not null,
   m_email varchar2(50) not null,
   m_phone varchar2(50) not null,
   m_point number default 0
)
--회원등록
insert into uri_member(m_id,m_password,m_name,m_ssn,m_address,m_email,m_phone)
values('java','a','박현종','1234561234567','판교','hj@kosta.com','018')

--시퀀스 생성 
create sequence pub_no_seq
--공용게시판 table 생성
drop table pub_board
create table pub_board(
   pub_no number primary key,
   pub_title varchar2(50) not null,
   pub_content varchar2(3000) not null,
   pub_date date not null,
   pub_hit number default 0,
   pub_like number default 0,
   ref number not null,
   restep number not null,
   relevel number not null,
   m_id varchar2(50) not null,
   constraint m_id_fk foreign key (m_id) references uri_member(m_id) on delete cascade
)
--공용게시판용 파일table 생성
create sequence pub_file_no_seq
drop table pub_file_attachment
create table pub_file_attachment(
	pub_file_no number primary key,
	pub_orgfilename varchar2(50) not null,
	pub_newfilename varchar2(50) not null,
	pub_no number not null,
	m_id varchar2(50) not null,
    constraint pub_file_attachment_fk_m_id foreign key (m_id) references uri_member(m_id),
    constraint pub_file_attachment_fk_pub_no foreign key (pub_no) references pub_board(pub_no) on delete cascade
)
--공용게시판용 댓글 table 생성
create sequence pub_c_no_seq
drop table pub_comment
create table pub_comment(
	pub_c_no number primary key,
	pub_c_content varchar2(1000) not null,
	pub_c_date date not null,
	m_id varchar2(50) not null,
	pub_no number not null,
	constraint pub_comment_fk_m_id foreign key (m_id) references uri_member(m_id),
	constraint pub_comment_fk_pub_no foreign key (pub_no) references pub_board(pub_no) on delete cascade
)

--pub 게시판 글쓰기
insert into pub_board(pub_no,pub_title,pub_content,pub_date,ref,restep,relevel,m_id)
values(pub_no_seq.nextval,'안녕','첫글입니다.',sysdate,1,0,0,'java');

--파일 업로드
insert into pub_file_attachment(pub_file_no,pub_orgfilename,pub_newfilename,pub_no,m_id)
values(pub_file_no_seq.nextval)
--모든 파일 객체 조회
select * from pub_file_attachment
--모든 게시물 검색
select * from pub_board order by ref desc,restep asc
--no에 해당하는 게시물 검색
select pub_no,pub_title,m_id,pub_hit,pub_like from pub_board where pub_no = 25

--1번 페이지의 대한 게시글 리스트
select pub_no,pub_title,m_id,pub_date,pub_hit,pub_like from (
			select rownum as rn,pub_no,pub_title,m_id,pub_date,pub_hit,pub_like,ceil(rownum/10) as page from (
				select pub_no,pub_title,m_id,to_char(pub_date,'YYYY/MM/DD')as pub_date,pub_hit,pub_like from 
					pub_board order by pub_no desc))where page = 1


select count(*) from PUB_BOARD

--해당 게시물 삭제 (삭제시 file 정보와 comment 정보도 함께 삭제됨)
delete from pub_board where pub_no = 48

--no에 해당하는 게시물의 모든 댓글 검색
select * from pub_comment where pub_no = 49
--no에 해당하는 게시물의 모든 파일객체 조회
select pub_newfilename,pub_orgfilename from pub_file_attachment where pub_no = 50
--no에 해당하는 파일객체 삭제
delete from pub_file_attachment where pub_file_no = 41

select * from pub_comment
select * from pub_file_attachment

--댓글번호에 해당하는 댓글 삭제
delete from PUB_COMMENT where pub_c_no = 44

--댓글번호에 해당하는 댓글 내용 수정
update PUB_COMMENT set pub_c_content = '나다' where pub_c_no = 47

--게시물번호에 해당하는 게시물 검색(번호,제목,내용,ref,restep,relevel)
select pub_no,pub_title,pub_content,ref,restep,relevel from pub_board where pub_no = 47

select count(*) from PUB_BOARD where pub_title like '%안%' or pub_content like '%안%'

--내용에 '첫'이라는 글자가 들어가는 게시물리스트 검색 (1페이지)
select pub_no,pub_title,m_id,pub_date,pub_hit,pub_like from (
			select rownum as rn,pub_no,pub_title,m_id,pub_date,pub_hit,pub_like,ceil(rownum/10) as page from (
				select pub_no,pub_title,m_id,to_char(pub_date,'YYYY/MM/DD')as pub_date,pub_hit,pub_like from 
					pub_board where pub_content like '%첫%'order by pub_no desc))where page = 1

--제목에 '안'이라는 글자가 들어가는 게시물리스트 검색 (1페이지) 
select pub_no,pub_title,m_id,pub_date,pub_hit,pub_like from (
			select rownum as rn,pub_no,pub_title,m_id,pub_date,pub_hit,pub_like,ceil(rownum/10) as page from (
				select pub_no,pub_title,m_id,to_char(pub_date,'YYYY/MM/DD')as pub_date,pub_hit,pub_like from 
					pub_board where pub_title like '%안%'order by pub_no desc))where page = 1

--제목이나 내용에 '안'이라는 글자가 포함된 게시물리스트 검색 (1페이지)					
select pub_no,pub_title,m_id,pub_date,pub_hit,pub_like from (
			select rownum as rn,pub_no,pub_title,m_id,pub_date,pub_hit,pub_like,ceil(rownum/10) as page from (
				select pub_no,pub_title,m_id,to_char(pub_date,'YYYY/MM/DD')as pub_date,pub_hit,pub_like from 
					pub_board where pub_title like '%안%' or pub_content like '%안%' order by pub_no desc))where page = 1

--no에 해당하는 게시글의 title과 content를 수정
update pub_board set pub_title = '아니야',pub_content = '안방가워' where pub_no = 70

--no에 해당하는 게시글 추천
update pub_board set pub_like = pub_like+1 where pub_no = 47

select * from pub_board